--
-- Test of Row-level security feature
--
-- Clean up in case a prior regression run failed
-- Suppress NOTICE messages when users/groups don't exist
SET client_min_messages TO 'warning';

DROP USER IF EXISTS regress_rls_alice;

DROP USER IF EXISTS regress_rls_bob;

DROP USER IF EXISTS regress_rls_carol;

DROP USER IF EXISTS regress_rls_dave;

DROP USER IF EXISTS regress_rls_exempt_user;

DROP ROLE IF EXISTS regress_rls_group1;

DROP ROLE IF EXISTS regress_rls_group2;

DROP SCHEMA IF EXISTS regress_rls_schema CASCADE;

RESET client_min_messages;

-- initial setup
CREATE USER regress_rls_alice NOLOGIN;

CREATE USER regress_rls_bob NOLOGIN;

CREATE USER regress_rls_carol NOLOGIN;

CREATE USER regress_rls_dave NOLOGIN;

CREATE USER regress_rls_exempt_user BYPASSRLS NOLOGIN;

CREATE ROLE regress_rls_group1 NOLOGIN;

CREATE ROLE regress_rls_group2 NOLOGIN;

GRANT regress_rls_group1 TO regress_rls_bob;

GRANT regress_rls_group2 TO regress_rls_carol;

CREATE SCHEMA regress_rls_schema;

GRANT ALL ON SCHEMA regress_rls_schema TO public;

SET search_path = regress_rls_schema;

-- setup of malicious function
CREATE OR REPLACE FUNCTION f_leak (text)
    RETURNS bool
    COST 0.0000001
    LANGUAGE plpgsql
    AS '
BEGIN
    RAISE NOTICE ''f_leak => %'', $1;

RETURN TRUE;

END;
';

GRANT EXECUTE ON FUNCTION f_leak (text) TO public;

-- BASIC Row-Level Security Scenario
SET SESSION AUTHORIZATION regress_rls_alice;

CREATE TABLE uaccount (
    pguser name PRIMARY KEY,
    seclv int
);

GRANT SELECT ON uaccount TO public;

INSERT INTO uaccount
VALUES
    ('regress_rls_alice', 99),
    ('regress_rls_bob', 1),
    ('regress_rls_carol', 2),
    ('regress_rls_dave', 3);

CREATE TABLE category (
    cid int PRIMARY KEY,
    cname text
);

GRANT ALL ON category TO public;

INSERT INTO category
VALUES
    (11, 'novel'),
    (22, 'science fiction'),
    (33, 'technology'),
    (44, 'manga');

CREATE TABLE document (
    did int PRIMARY KEY,
    cid int REFERENCES category (cid),
    dlevel int NOT NULL,
    dauthor name,
    dtitle text
);

GRANT ALL ON document TO public;

INSERT INTO document
VALUES
    (1, 11, 1, 'regress_rls_bob', 'my first novel'),
    (2, 11, 2, 'regress_rls_bob', 'my second novel'),
    (3, 22, 2, 'regress_rls_bob', 'my science fiction'),
    (4, 44, 1, 'regress_rls_bob', 'my first manga'),
    (5, 44, 2, 'regress_rls_bob', 'my second manga'),
    (6, 22, 1, 'regress_rls_carol', 'great science fiction'),
    (7, 33, 2, 'regress_rls_carol', 'great technology book'),
    (8, 44, 1, 'regress_rls_carol', 'great manga'),
    (9, 22, 1, 'regress_rls_dave', 'awesome science fiction'),
    (10, 33, 2, 'regress_rls_dave', 'awesome technology book');

ALTER TABLE document ENABLE ROW LEVEL SECURITY;

-- user's security level must be higher than or equal to document's
CREATE POLICY p1 ON document AS PERMISSIVE
    USING (dlevel <= (
        SELECT
            seclv
        FROM
            uaccount
        WHERE
            pguser = CURRENT_USER));

-- try to create a policy of bogus type
CREATE POLICY p1 ON document AS UGLY
    USING (dlevel <= (
        SELECT
            seclv
        FROM
            uaccount
        WHERE
            pguser = CURRENT_USER));

-- but Dave isn't allowed to anything at cid 50 or above
-- this is to make sure that we sort the policies by name first
-- when applying WITH CHECK, a later INSERT by Dave should fail due
-- to p1r first
CREATE POLICY p2r ON document AS RESTRICTIVE TO regress_rls_dave
    USING (cid <> 44
        AND cid < 50);

-- and Dave isn't allowed to see manga documents
CREATE POLICY p1r ON document AS RESTRICTIVE TO regress_rls_dave
    USING (cid <> 44);

\dp
\d document
SELECT
    *
FROM
    pg_policies
WHERE
    schemaname = 'regress_rls_schema'
    AND tablename = 'document'
ORDER BY
    policyname;

-- viewpoint from regress_rls_bob
SET SESSION AUTHORIZATION regress_rls_bob;

SET row_security TO ON;

SELECT
    *
FROM
    document
WHERE
    f_leak (dtitle)
ORDER BY
    did;

SELECT
    *
FROM
    document
    NATURAL JOIN category
WHERE
    f_leak (dtitle)
ORDER BY
    did;

-- try a sampled version
SELECT
    *
FROM
    document TABLESAMPLE BERNOULLI (50)
    REPEATABLE (0)
WHERE
    f_leak (dtitle)
ORDER BY
    did;

-- viewpoint from regress_rls_carol
SET SESSION AUTHORIZATION regress_rls_carol;

SELECT
    *
FROM
    document
WHERE
    f_leak (dtitle)
ORDER BY
    did;

SELECT
    *
FROM
    document
    NATURAL JOIN category
WHERE
    f_leak (dtitle)
ORDER BY
    did;

-- try a sampled version
SELECT
    *
FROM
    document TABLESAMPLE BERNOULLI (50)
    REPEATABLE (0)
WHERE
    f_leak (dtitle)
ORDER BY
    did;

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    document
WHERE
    f_leak (dtitle);

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    document
    NATURAL JOIN category
WHERE
    f_leak (dtitle);

-- viewpoint from regress_rls_dave
SET SESSION AUTHORIZATION regress_rls_dave;

SELECT
    *
FROM
    document
WHERE
    f_leak (dtitle)
ORDER BY
    did;

SELECT
    *
FROM
    document
    NATURAL JOIN category
WHERE
    f_leak (dtitle)
ORDER BY
    did;

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    document
WHERE
    f_leak (dtitle);

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    document
    NATURAL JOIN category
WHERE
    f_leak (dtitle);

-- 44 would technically fail for both p2r and p1r, but we should get an error
-- back from p1r for this because it sorts first
INSERT INTO document
    VALUES (100, 44, 1, 'regress_rls_dave', 'testing sorting of policies');

-- fail
-- Just to see a p2r error
INSERT INTO document
    VALUES (100, 55, 1, 'regress_rls_dave', 'testing sorting of policies');

-- fail
-- only owner can change policies
ALTER POLICY p1 ON document
    USING (TRUE);

--fail
DROP POLICY p1 ON document;

--fail
SET SESSION AUTHORIZATION regress_rls_alice;

ALTER POLICY p1 ON document
    USING (dauthor = CURRENT_USER);

-- viewpoint from regress_rls_bob again
SET SESSION AUTHORIZATION regress_rls_bob;

SELECT
    *
FROM
    document
WHERE
    f_leak (dtitle)
ORDER BY
    did;

SELECT
    *
FROM
    document
    NATURAL JOIN category
WHERE
    f_leak (dtitle)
ORDER BY
    did;

-- viewpoint from rls_regres_carol again
SET SESSION AUTHORIZATION regress_rls_carol;

SELECT
    *
FROM
    document
WHERE
    f_leak (dtitle)
ORDER BY
    did;

SELECT
    *
FROM
    document
    NATURAL JOIN category
WHERE
    f_leak (dtitle)
ORDER BY
    did;

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    document
WHERE
    f_leak (dtitle);

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    document
    NATURAL JOIN category
WHERE
    f_leak (dtitle);

-- interaction of FK/PK constraints
SET SESSION AUTHORIZATION regress_rls_alice;

CREATE POLICY p2 ON category
    USING (
        CASE WHEN CURRENT_USER = 'regress_rls_bob' THEN
            cid IN (11, 33)
        WHEN CURRENT_USER = 'regress_rls_carol' THEN
            cid IN (22, 44)
        ELSE
            FALSE
        END);

ALTER TABLE category ENABLE ROW LEVEL SECURITY;

-- cannot delete PK referenced by invisible FK
SET SESSION AUTHORIZATION regress_rls_bob;

SELECT
    *
FROM
    document d
    FULL OUTER JOIN category c ON d.cid = c.cid
ORDER BY
    d.did,
    c.cid;

DELETE FROM category
WHERE cid = 33;

-- fails with FK violation
-- can insert FK referencing invisible PK
SET SESSION AUTHORIZATION regress_rls_carol;

SELECT
    *
FROM
    document d
    FULL OUTER JOIN category c ON d.cid = c.cid
ORDER BY
    d.did,
    c.cid;

INSERT INTO document
    VALUES (11, 33, 1, CURRENT_USER, 'hoge');

-- UNIQUE or PRIMARY KEY constraint violation DOES reveal presence of row
SET SESSION AUTHORIZATION regress_rls_bob;

INSERT INTO document
    VALUES (8, 44, 1, 'regress_rls_bob', 'my third manga');

-- Must fail with unique violation, revealing presence of did we can't see
SELECT
    *
FROM
    document
WHERE
    did = 8;

-- and confirm we can't see it
-- RLS policies are checked before constraints
INSERT INTO document
    VALUES (8, 44, 1, 'regress_rls_carol', 'my third manga');

-- Should fail with RLS check violation, not duplicate key violation
UPDATE
    document
SET
    did = 8,
    dauthor = 'regress_rls_carol'
WHERE
    did = 5;

-- Should fail with RLS check violation, not duplicate key violation
-- database superuser does bypass RLS policy when enabled
RESET SESSION AUTHORIZATION;

SET row_security TO ON;

SELECT
    *
FROM
    document;

SELECT
    *
FROM
    category;

-- database superuser does bypass RLS policy when disabled
RESET SESSION AUTHORIZATION;

SET row_security TO OFF;

SELECT
    *
FROM
    document;

SELECT
    *
FROM
    category;

-- database non-superuser with bypass privilege can bypass RLS policy when disabled
SET SESSION AUTHORIZATION regress_rls_exempt_user;

SET row_security TO OFF;

SELECT
    *
FROM
    document;

SELECT
    *
FROM
    category;

-- RLS policy does not apply to table owner when RLS enabled.
SET SESSION AUTHORIZATION regress_rls_alice;

SET row_security TO ON;

SELECT
    *
FROM
    document;

SELECT
    *
FROM
    category;

-- RLS policy does not apply to table owner when RLS disabled.
SET SESSION AUTHORIZATION regress_rls_alice;

SET row_security TO OFF;

SELECT
    *
FROM
    document;

SELECT
    *
FROM
    category;

--
-- Table inheritance and RLS policy
--
SET SESSION AUTHORIZATION regress_rls_alice;

SET row_security TO ON;

CREATE TABLE t1 (
    id int NOT NULL PRIMARY KEY,
    a int,
    junk1 text,
    b text
);

ALTER TABLE t1
    DROP COLUMN junk1;

-- just a disturbing factor
GRANT ALL ON t1 TO public;

CREATE TABLE t2 (
    c float
)
INHERITS (
    t1
);

GRANT ALL ON t2 TO public;

CREATE TABLE t3 (
    id int NOT NULL PRIMARY KEY,
    c text,
    b text,
    a int
);

ALTER TABLE t3 INHERIT t1;

GRANT ALL ON t3 TO public;

CREATE POLICY p1 ON t1
    FOR ALL TO PUBLIC
        USING (a % 2 = 0);

-- be even number
CREATE POLICY p2 ON t2
    FOR ALL TO PUBLIC
        USING (a % 2 = 1);

-- be odd number
ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;

ALTER TABLE t2 ENABLE ROW LEVEL SECURITY;

SET SESSION AUTHORIZATION regress_rls_bob;

SELECT
    *
FROM
    t1;

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    t1;

SELECT
    *
FROM
    t1
WHERE
    f_leak (b);

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    t1
WHERE
    f_leak (b);

-- reference to system column
SELECT
    tableoid::regclass,
    *
FROM
    t1;

EXPLAIN (
    COSTS OFF
)
SELECT
    *,
    t1
FROM
    t1;

-- reference to whole-row reference
SELECT
    *,
    t1
FROM
    t1;

EXPLAIN (
    COSTS OFF
)
SELECT
    *,
    t1
FROM
    t1;

-- for share/update lock
SELECT
    *
FROM
    t1 FOR SHARE;

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    t1 FOR SHARE;

SELECT
    *
FROM
    t1
WHERE
    f_leak (b)
    FOR SHARE;

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    t1
WHERE
    f_leak (b)
    FOR SHARE;

-- union all query
SELECT
    a,
    b,
    tableoid::regclass
FROM
    t2
UNION ALL
SELECT
    a,
    b,
    tableoid::regclass
FROM
    t3;

EXPLAIN (
    COSTS OFF
)
SELECT
    a,
    b,
    tableoid::regclass
FROM
    t2
UNION ALL
SELECT
    a,
    b,
    tableoid::regclass
FROM
    t3;

-- superuser is allowed to bypass RLS checks
RESET SESSION AUTHORIZATION;

SET row_security TO OFF;

SELECT
    *
FROM
    t1
WHERE
    f_leak (b);

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    t1
WHERE
    f_leak (b);

-- non-superuser with bypass privilege can bypass RLS policy when disabled
SET SESSION AUTHORIZATION regress_rls_exempt_user;

SET row_security TO OFF;

SELECT
    *
FROM
    t1
WHERE
    f_leak (b);

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    t1
WHERE
    f_leak (b);

--
-- Partitioned Tables
--
SET SESSION AUTHORIZATION regress_rls_alice;

CREATE TABLE part_document (
    did int,
    cid int,
    dlevel int NOT NULL,
    dauthor name,
    dtitle text
)
PARTITION BY RANGE (cid);

GRANT ALL ON part_document TO public;

-- Create partitions for document categories
CREATE TABLE part_document_fiction PARTITION OF part_document
FOR VALUES FROM (11) TO (12);

CREATE TABLE part_document_satire PARTITION OF part_document
FOR VALUES FROM (55) TO (56);

CREATE TABLE part_document_nonfiction PARTITION OF part_document
FOR VALUES FROM (99) TO (100);

GRANT ALL ON part_document_fiction TO public;

GRANT ALL ON part_document_satire TO public;

GRANT ALL ON part_document_nonfiction TO public;

INSERT INTO part_document
VALUES
    (1, 11, 1, 'regress_rls_bob', 'my first novel'),
    (2, 11, 2, 'regress_rls_bob', 'my second novel'),
    (3, 99, 2, 'regress_rls_bob', 'my science textbook'),
    (4, 55, 1, 'regress_rls_bob', 'my first satire'),
    (5, 99, 2, 'regress_rls_bob', 'my history book'),
    (6, 11, 1, 'regress_rls_carol', 'great science fiction'),
    (7, 99, 2, 'regress_rls_carol', 'great technology book'),
    (8, 55, 2, 'regress_rls_carol', 'great satire'),
    (9, 11, 1, 'regress_rls_dave', 'awesome science fiction'),
    (10, 99, 2, 'regress_rls_dave', 'awesome technology book');

ALTER TABLE part_document ENABLE ROW LEVEL SECURITY;

-- Create policy on parent
-- user's security level must be higher than or equal to document's
CREATE POLICY pp1 ON part_document AS PERMISSIVE
    USING (dlevel <= (
        SELECT
            seclv
        FROM
            uaccount
        WHERE
            pguser = CURRENT_USER));

-- Dave is only allowed to see cid < 55
CREATE POLICY pp1r ON part_document AS RESTRICTIVE TO regress_rls_dave
    USING (cid < 55);

\d+ part_document
SELECT
    *
FROM
    pg_policies
WHERE
    schemaname = 'regress_rls_schema'
    AND tablename LIKE '%part_document%'
ORDER BY
    policyname;

-- viewpoint from regress_rls_bob
SET SESSION AUTHORIZATION regress_rls_bob;

SET row_security TO ON;

SELECT
    *
FROM
    part_document
WHERE
    f_leak (dtitle)
ORDER BY
    did;

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    part_document
WHERE
    f_leak (dtitle);

-- viewpoint from regress_rls_carol
SET SESSION AUTHORIZATION regress_rls_carol;

SELECT
    *
FROM
    part_document
WHERE
    f_leak (dtitle)
ORDER BY
    did;

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    part_document
WHERE
    f_leak (dtitle);

-- viewpoint from regress_rls_dave
SET SESSION AUTHORIZATION regress_rls_dave;

SELECT
    *
FROM
    part_document
WHERE
    f_leak (dtitle)
ORDER BY
    did;

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    part_document
WHERE
    f_leak (dtitle);

-- pp1 ERROR
INSERT INTO part_document
    VALUES (100, 11, 5, 'regress_rls_dave', 'testing pp1');

-- fail
-- pp1r ERROR
INSERT INTO part_document
    VALUES (100, 99, 1, 'regress_rls_dave', 'testing pp1r');

-- fail
-- Show that RLS policy does not apply for direct inserts to children
-- This should fail with RLS POLICY pp1r violation.
INSERT INTO part_document
    VALUES (100, 55, 1, 'regress_rls_dave', 'testing RLS with partitions');

-- fail
-- But this should succeed.
INSERT INTO part_document_satire
    VALUES (100, 55, 1, 'regress_rls_dave', 'testing RLS with partitions');

-- success
-- We still cannot see the row using the parent
SELECT
    *
FROM
    part_document
WHERE
    f_leak (dtitle)
ORDER BY
    did;

-- But we can if we look directly
SELECT
    *
FROM
    part_document_satire
WHERE
    f_leak (dtitle)
ORDER BY
    did;

-- Turn on RLS and create policy on child to show RLS is checked before constraints
SET SESSION AUTHORIZATION regress_rls_alice;

ALTER TABLE part_document_satire ENABLE ROW LEVEL SECURITY;

CREATE POLICY pp3 ON part_document_satire AS RESTRICTIVE
    USING (cid < 55);

-- This should fail with RLS violation now.
SET SESSION AUTHORIZATION regress_rls_dave;

INSERT INTO part_document_satire
    VALUES (101, 55, 1, 'regress_rls_dave', 'testing RLS with partitions');

-- fail
-- And now we cannot see directly into the partition either, due to RLS
SELECT
    *
FROM
    part_document_satire
WHERE
    f_leak (dtitle)
ORDER BY
    did;

-- The parent looks same as before
-- viewpoint from regress_rls_dave
SELECT
    *
FROM
    part_document
WHERE
    f_leak (dtitle)
ORDER BY
    did;

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    part_document
WHERE
    f_leak (dtitle);

-- viewpoint from regress_rls_carol
SET SESSION AUTHORIZATION regress_rls_carol;

SELECT
    *
FROM
    part_document
WHERE
    f_leak (dtitle)
ORDER BY
    did;

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    part_document
WHERE
    f_leak (dtitle);

-- only owner can change policies
ALTER POLICY pp1 ON part_document
    USING (TRUE);

--fail
DROP POLICY pp1 ON part_document;

--fail
SET SESSION AUTHORIZATION regress_rls_alice;

ALTER POLICY pp1 ON part_document
    USING (dauthor = CURRENT_USER);

-- viewpoint from regress_rls_bob again
SET SESSION AUTHORIZATION regress_rls_bob;

SELECT
    *
FROM
    part_document
WHERE
    f_leak (dtitle)
ORDER BY
    did;

-- viewpoint from rls_regres_carol again
SET SESSION AUTHORIZATION regress_rls_carol;

SELECT
    *
FROM
    part_document
WHERE
    f_leak (dtitle)
ORDER BY
    did;

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    part_document
WHERE
    f_leak (dtitle);

-- database superuser does bypass RLS policy when enabled
RESET SESSION AUTHORIZATION;

SET row_security TO ON;

SELECT
    *
FROM
    part_document
ORDER BY
    did;

SELECT
    *
FROM
    part_document_satire
ORDER BY
    did;

-- database non-superuser with bypass privilege can bypass RLS policy when disabled
SET SESSION AUTHORIZATION regress_rls_exempt_user;

SET row_security TO OFF;

SELECT
    *
FROM
    part_document
ORDER BY
    did;

SELECT
    *
FROM
    part_document_satire
ORDER BY
    did;

-- RLS policy does not apply to table owner when RLS enabled.
SET SESSION AUTHORIZATION regress_rls_alice;

SET row_security TO ON;

SELECT
    *
FROM
    part_document
ORDER BY
    did;

SELECT
    *
FROM
    part_document_satire
ORDER BY
    did;

-- When RLS disabled, other users get ERROR.
SET SESSION AUTHORIZATION regress_rls_dave;

SET row_security TO OFF;

SELECT
    *
FROM
    part_document
ORDER BY
    did;

SELECT
    *
FROM
    part_document_satire
ORDER BY
    did;

-- Check behavior with a policy that uses a SubPlan not an InitPlan.
SET SESSION AUTHORIZATION regress_rls_alice;

SET row_security TO ON;

CREATE POLICY pp3 ON part_document AS RESTRICTIVE
    USING ((
        SELECT
            dlevel <= seclv
        FROM
            uaccount
        WHERE
            pguser = CURRENT_USER));

SET SESSION AUTHORIZATION regress_rls_carol;

INSERT INTO part_document
    VALUES (100, 11, 5, 'regress_rls_carol', 'testing pp3');

-- fail
----- Dependencies -----
SET SESSION AUTHORIZATION regress_rls_alice;

SET row_security TO ON;

CREATE TABLE dependee (
    x integer,
    y integer
);

CREATE TABLE dependent (
    x integer,
    y integer
);

CREATE POLICY d1 ON dependent
    FOR ALL TO PUBLIC
        USING (x = (
            SELECT
                d.x
            FROM
                dependee d
            WHERE
                d.y = y));

DROP TABLE dependee;

-- Should fail without CASCADE due to dependency on row security qual?
DROP TABLE dependee CASCADE;

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    dependent;

-- After drop, should be unqualified
-----   RECURSION    ----
--
-- Simple recursion
--
SET SESSION AUTHORIZATION regress_rls_alice;

CREATE TABLE rec1 (
    x integer,
    y integer
);

CREATE POLICY r1 ON rec1
    USING (x = (
        SELECT
            r.x
        FROM
            rec1 r
        WHERE
            y = r.y));

ALTER TABLE rec1 ENABLE ROW LEVEL SECURITY;

SET SESSION AUTHORIZATION regress_rls_bob;

SELECT
    *
FROM
    rec1;

-- fail, direct recursion
--
-- Mutual recursion
--
SET SESSION AUTHORIZATION regress_rls_alice;

CREATE TABLE rec2 (
    a integer,
    b integer
);

ALTER POLICY r1 ON rec1
    USING (x = (
        SELECT
            a FROM rec2
        WHERE
            b = y));

CREATE POLICY r2 ON rec2
    USING (a = (
        SELECT
            x
        FROM
            rec1
        WHERE
            y = b));

ALTER TABLE rec2 ENABLE ROW LEVEL SECURITY;

SET SESSION AUTHORIZATION regress_rls_bob;

SELECT
    *
FROM
    rec1;

-- fail, mutual recursion
--
-- Mutual recursion via views
--
SET SESSION AUTHORIZATION regress_rls_bob;

CREATE VIEW rec1v AS
SELECT
    *
FROM
    rec1;

CREATE VIEW rec2v AS
SELECT
    *
FROM
    rec2;

SET SESSION AUTHORIZATION regress_rls_alice;

ALTER POLICY r1 ON rec1
    USING (x = (
        SELECT
            a FROM rec2v
        WHERE
            b = y));

ALTER POLICY r2 ON rec2
    USING (a = (
        SELECT
            x FROM rec1v
        WHERE
            y = b));

SET SESSION AUTHORIZATION regress_rls_bob;

SELECT
    *
FROM
    rec1;

-- fail, mutual recursion via views
--
-- Mutual recursion via .s.b views
--
SET SESSION AUTHORIZATION regress_rls_bob;

DROP VIEW rec1v, rec2v CASCADE;

CREATE VIEW rec1v WITH ( security_barrier
) AS
SELECT
    *
FROM
    rec1;

CREATE VIEW rec2v WITH ( security_barrier
) AS
SELECT
    *
FROM
    rec2;

SET SESSION AUTHORIZATION regress_rls_alice;

CREATE POLICY r1 ON rec1
    USING (x = (
        SELECT
            a
        FROM
            rec2v
        WHERE
            b = y));

CREATE POLICY r2 ON rec2
    USING (a = (
        SELECT
            x
        FROM
            rec1v
        WHERE
            y = b));

SET SESSION AUTHORIZATION regress_rls_bob;

SELECT
    *
FROM
    rec1;

-- fail, mutual recursion via s.b. views
--
-- recursive RLS and VIEWs in policy
--
SET SESSION AUTHORIZATION regress_rls_alice;

CREATE TABLE s1 (
    a int,
    b text
);

INSERT INTO s1 (
    SELECT
        x,
        md5(x::text)
    FROM
        generate_series(-10, 10) x);

CREATE TABLE s2 (
    x int,
    y text
);

INSERT INTO s2 (
    SELECT
        x,
        md5(x::text)
    FROM
        generate_series(-6, 6) x);

GRANT SELECT ON s1, s2 TO regress_rls_bob;

CREATE POLICY p1 ON s1
    USING (a IN (
        SELECT
            x
        FROM
            s2
        WHERE
            y LIKE '%2f%'));

CREATE POLICY p2 ON s2
    USING (x IN (
        SELECT
            a
        FROM
            s1
        WHERE
            b LIKE '%22%'));

CREATE POLICY p3 ON s1
    FOR INSERT
        WITH CHECK (a = (
            SELECT
                a
            FROM
                s1));

ALTER TABLE s1 ENABLE ROW LEVEL SECURITY;

ALTER TABLE s2 ENABLE ROW LEVEL SECURITY;

SET SESSION AUTHORIZATION regress_rls_bob;

CREATE VIEW v2 AS
SELECT
    *
FROM
    s2
WHERE
    y LIKE '%af%';

SELECT
    *
FROM
    s1
WHERE
    f_leak (b);

-- fail (infinite recursion)
INSERT INTO s1
    VALUES (1, 'foo');

-- fail (infinite recursion)
SET SESSION AUTHORIZATION regress_rls_alice;

DROP POLICY p3 ON s1;

ALTER POLICY p2 ON s2
    USING (x % 2 = 0);

SET SESSION AUTHORIZATION regress_rls_bob;

SELECT
    *
FROM
    s1
WHERE
    f_leak (b);

-- OK
EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    ONLY s1
WHERE
    f_leak (b);

SET SESSION AUTHORIZATION regress_rls_alice;

ALTER POLICY p1 ON s1
    USING (a IN (
        SELECT
            x FROM v2));

-- using VIEW in RLS policy
SET SESSION AUTHORIZATION regress_rls_bob;

SELECT
    *
FROM
    s1
WHERE
    f_leak (b);

-- OK
EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    s1
WHERE
    f_leak (b);

SELECT
    (
        SELECT
            x
        FROM
            s1
        LIMIT 1) xx,
    *
FROM
    s2
WHERE
    y LIKE '%28%';

EXPLAIN (
    COSTS OFF
)
SELECT
    (
        SELECT
            x
        FROM
            s1
        LIMIT 1) xx,
    *
FROM
    s2
WHERE
    y LIKE '%28%';

SET SESSION AUTHORIZATION regress_rls_alice;

ALTER POLICY p2 ON s2
    USING (x IN (
        SELECT
            a FROM s1
        WHERE
            b LIKE '%d2%'));

SET SESSION AUTHORIZATION regress_rls_bob;

SELECT
    *
FROM
    s1
WHERE
    f_leak (b);

-- fail (infinite recursion via view)
-- prepared statement with regress_rls_alice privilege
PREPARE p1 (int) AS
SELECT
    *
FROM
    t1
WHERE
    a <= $1;

EXECUTE p1 (2);

EXPLAIN (
    COSTS OFF
) EXECUTE p1 (2);

-- superuser is allowed to bypass RLS checks
RESET SESSION AUTHORIZATION;

SET row_security TO OFF;

SELECT
    *
FROM
    t1
WHERE
    f_leak (b);

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    t1
WHERE
    f_leak (b);

-- plan cache should be invalidated
EXECUTE p1 (2);

EXPLAIN (
    COSTS OFF
) EXECUTE p1 (2);

PREPARE p2 (int) AS
SELECT
    *
FROM
    t1
WHERE
    a = $1;

EXECUTE p2 (2);

EXPLAIN (
    COSTS OFF
) EXECUTE p2 (2);

-- also, case when privilege switch from superuser
SET SESSION AUTHORIZATION regress_rls_bob;

SET row_security TO ON;

EXECUTE p2 (2);

EXPLAIN (
    COSTS OFF
) EXECUTE p2 (2);

--
-- UPDATE / DELETE and Row-level security
--
SET SESSION AUTHORIZATION regress_rls_bob;

EXPLAIN (
    COSTS OFF
) UPDATE
    t1
SET
    b = b || b
WHERE
    f_leak (b);

UPDATE
    t1
SET
    b = b || b
WHERE
    f_leak (b);

EXPLAIN (
    COSTS OFF
) UPDATE
    ONLY t1
SET
    b = b || '_updt'
WHERE
    f_leak (b);

UPDATE
    ONLY t1
SET
    b = b || '_updt'
WHERE
    f_leak (b);

-- returning clause with system column
UPDATE
    ONLY t1
SET
    b = b
WHERE
    f_leak (b)
RETURNING
    tableoid::regclass,
    *,
    t1;

UPDATE
    t1
SET
    b = b
WHERE
    f_leak (b)
RETURNING
    *;

UPDATE
    t1
SET
    b = b
WHERE
    f_leak (b)
RETURNING
    tableoid::regclass,
    *,
    t1;

-- updates with from clause
EXPLAIN (
    COSTS OFF
) UPDATE
    t2
SET
    b = t2.b
FROM
    t3
WHERE
    t2.a = 3
    AND t3.a = 2
    AND f_leak (t2.b)
    AND f_leak (t3.b);

UPDATE
    t2
SET
    b = t2.b
FROM
    t3
WHERE
    t2.a = 3
    AND t3.a = 2
    AND f_leak (t2.b)
    AND f_leak (t3.b);

EXPLAIN (
    COSTS OFF
) UPDATE
    t1
SET
    b = t1.b
FROM
    t2
WHERE
    t1.a = 3
    AND t2.a = 3
    AND f_leak (t1.b)
    AND f_leak (t2.b);

UPDATE
    t1
SET
    b = t1.b
FROM
    t2
WHERE
    t1.a = 3
    AND t2.a = 3
    AND f_leak (t1.b)
    AND f_leak (t2.b);

EXPLAIN (
    COSTS OFF
) UPDATE
    t2
SET
    b = t2.b
FROM
    t1
WHERE
    t1.a = 3
    AND t2.a = 3
    AND f_leak (t1.b)
    AND f_leak (t2.b);

UPDATE
    t2
SET
    b = t2.b
FROM
    t1
WHERE
    t1.a = 3
    AND t2.a = 3
    AND f_leak (t1.b)
    AND f_leak (t2.b);

-- updates with from clause self join
EXPLAIN (
    COSTS OFF
) UPDATE
    t2 t2_1
SET
    b = t2_2.b
FROM
    t2 t2_2
WHERE
    t2_1.a = 3
    AND t2_2.a = t2_1.a
    AND t2_2.b = t2_1.b
    AND f_leak (t2_1.b)
    AND f_leak (t2_2.b)
RETURNING
    *,
    t2_1,
    t2_2;

UPDATE
    t2 t2_1
SET
    b = t2_2.b
FROM
    t2 t2_2
WHERE
    t2_1.a = 3
    AND t2_2.a = t2_1.a
    AND t2_2.b = t2_1.b
    AND f_leak (t2_1.b)
    AND f_leak (t2_2.b)
RETURNING
    *,
    t2_1,
    t2_2;

EXPLAIN (
    COSTS OFF
) UPDATE
    t1 t1_1
SET
    b = t1_2.b
FROM
    t1 t1_2
WHERE
    t1_1.a = 4
    AND t1_2.a = t1_1.a
    AND t1_2.b = t1_1.b
    AND f_leak (t1_1.b)
    AND f_leak (t1_2.b)
RETURNING
    *,
    t1_1,
    t1_2;

UPDATE
    t1 t1_1
SET
    b = t1_2.b
FROM
    t1 t1_2
WHERE
    t1_1.a = 4
    AND t1_2.a = t1_1.a
    AND t1_2.b = t1_1.b
    AND f_leak (t1_1.b)
    AND f_leak (t1_2.b)
RETURNING
    *,
    t1_1,
    t1_2;

RESET SESSION AUTHORIZATION;

SET row_security TO OFF;

SELECT
    *
FROM
    t1
ORDER BY
    a,
    b;

SET SESSION AUTHORIZATION regress_rls_bob;

SET row_security TO ON;

EXPLAIN (
    COSTS OFF
) DELETE FROM ONLY t1
WHERE f_leak (b);

EXPLAIN (
    COSTS OFF
) DELETE FROM t1
WHERE f_leak (b);

DELETE FROM ONLY t1
WHERE f_leak (b)
RETURNING
    tableoid::regclass,
    *,
    t1;

DELETE FROM t1
WHERE f_leak (b)
RETURNING
    tableoid::regclass,
    *,
    t1;

--
-- S.b. view on top of Row-level security
--
SET SESSION AUTHORIZATION regress_rls_alice;

CREATE TABLE b1 (
    a int,
    b text
);

INSERT INTO b1 (
    SELECT
        x,
        md5(x::text)
    FROM
        generate_series(-10, 10) x);

CREATE POLICY p1 ON b1
    USING (a % 2 = 0);

ALTER TABLE b1 ENABLE ROW LEVEL SECURITY;

GRANT ALL ON b1 TO regress_rls_bob;

SET SESSION AUTHORIZATION regress_rls_bob;

CREATE VIEW bv1 WITH ( security_barrier
) AS
SELECT
    *
FROM
    b1
WHERE
    a > 0 WITH CHECK OPTION;

GRANT ALL ON bv1 TO regress_rls_carol;

SET SESSION AUTHORIZATION regress_rls_carol;

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    bv1
WHERE
    f_leak (b);

SELECT
    *
FROM
    bv1
WHERE
    f_leak (b);

INSERT INTO bv1
    VALUES (-1, 'xxx');

-- should fail view WCO
INSERT INTO bv1
    VALUES (11, 'xxx');

-- should fail RLS check
INSERT INTO bv1
    VALUES (12, 'xxx');

-- ok
EXPLAIN (
    COSTS OFF
) UPDATE
    bv1
SET
    b = 'yyy'
WHERE
    a = 4
    AND f_leak (b);

UPDATE
    bv1
SET
    b = 'yyy'
WHERE
    a = 4
    AND f_leak (b);

EXPLAIN (
    COSTS OFF
) DELETE FROM bv1
WHERE a = 6
    AND f_leak (b);

DELETE FROM bv1
WHERE a = 6
    AND f_leak (b);

SET SESSION AUTHORIZATION regress_rls_alice;

SELECT
    *
FROM
    b1;

--
-- INSERT ... ON CONFLICT DO UPDATE and Row-level security
--
SET SESSION AUTHORIZATION regress_rls_alice;

DROP POLICY p1 ON document;

DROP POLICY p1r ON document;

CREATE POLICY p1 ON document
    FOR SELECT
        USING (TRUE);

CREATE POLICY p2 ON document
    FOR INSERT
        WITH CHECK (dauthor = CURRENT_USER);

CREATE POLICY p3 ON document
    FOR UPDATE
        USING (cid = (
            SELECT
                cid
            FROM
                category
            WHERE
                cname = 'novel'))
            WITH CHECK (dauthor = CURRENT_USER);

SET SESSION AUTHORIZATION regress_rls_bob;

-- Exists...
SELECT
    *
FROM
    document
WHERE
    did = 2;

-- ...so violates actual WITH CHECK OPTION within UPDATE (not INSERT, since
-- alternative UPDATE path happens to be taken):
INSERT INTO document
    VALUES (2, (
            SELECT
                cid
            FROM category
            WHERE
                cname = 'novel'), 1, 'regress_rls_carol', 'my first novel')
ON CONFLICT (did)
    DO UPDATE SET
        dtitle = EXCLUDED.dtitle,
        dauthor = EXCLUDED.dauthor;

-- Violates USING qual for UPDATE policy p3.
--
-- UPDATE path is taken, but UPDATE fails purely because *existing* row to be
-- updated is not a "novel"/cid 11 (row is not leaked, even though we have
-- SELECT privileges sufficient to see the row in this instance):
INSERT INTO document
    VALUES (33, 22, 1, 'regress_rls_bob', 'okay science fiction');

-- preparation for next statement
INSERT INTO document
    VALUES (33, (
            SELECT
                cid
            FROM category
            WHERE
                cname = 'novel'), 1, 'regress_rls_bob', 'Some novel, replaces sci-fi')
-- takes UPDATE path
ON CONFLICT (did)
    DO UPDATE SET
        dtitle = EXCLUDED.dtitle;

-- Fine (we UPDATE, since INSERT WCOs and UPDATE security barrier quals + WCOs
-- not violated):
INSERT INTO document
    VALUES (2, (
            SELECT
                cid
            FROM category
            WHERE
                cname = 'novel'), 1, 'regress_rls_bob', 'my first novel')
ON CONFLICT (did)
    DO UPDATE SET
        dtitle = EXCLUDED.dtitle
    RETURNING
        *;

-- Fine (we INSERT, so "cid = 33" ("technology") isn't evaluated):
INSERT INTO document
    VALUES (78, (
            SELECT
                cid
            FROM category
            WHERE
                cname = 'novel'), 1, 'regress_rls_bob', 'some technology novel')
ON CONFLICT (did)
    DO UPDATE SET
        dtitle = EXCLUDED.dtitle,
        cid = 33
    RETURNING
        *;

-- Fine (same query, but we UPDATE, so "cid = 33", ("technology") is not the
-- case in respect of *existing* tuple):
INSERT INTO document
    VALUES (78, (
            SELECT
                cid
            FROM category
            WHERE
                cname = 'novel'), 1, 'regress_rls_bob', 'some technology novel')
ON CONFLICT (did)
    DO UPDATE SET
        dtitle = EXCLUDED.dtitle,
        cid = 33
    RETURNING
        *;

-- Same query a third time, but now fails due to existing tuple finally not
-- passing quals:
INSERT INTO document
    VALUES (78, (
            SELECT
                cid
            FROM category
            WHERE
                cname = 'novel'), 1, 'regress_rls_bob', 'some technology novel')
ON CONFLICT (did)
    DO UPDATE SET
        dtitle = EXCLUDED.dtitle,
        cid = 33
    RETURNING
        *;

-- Don't fail just because INSERT doesn't satisfy WITH CHECK option that
-- originated as a barrier/USING() qual from the UPDATE.  Note that the UPDATE
-- path *isn't* taken, and so UPDATE-related policy does not apply:
INSERT INTO document
    VALUES (79, (
            SELECT
                cid
            FROM category
            WHERE
                cname = 'technology'), 1, 'regress_rls_bob', 'technology book, can only insert')
ON CONFLICT (did)
    DO UPDATE SET
        dtitle = EXCLUDED.dtitle
    RETURNING
        *;

-- But this time, the same statement fails, because the UPDATE path is taken,
-- and updating the row just inserted falls afoul of security barrier qual
-- (enforced as WCO) -- what we might have updated target tuple to is
-- irrelevant, in fact.
INSERT INTO document
    VALUES (79, (
            SELECT
                cid
            FROM category
            WHERE
                cname = 'technology'), 1, 'regress_rls_bob', 'technology book, can only insert')
ON CONFLICT (did)
    DO UPDATE SET
        dtitle = EXCLUDED.dtitle
    RETURNING
        *;

-- Test default USING qual enforced as WCO
SET SESSION AUTHORIZATION regress_rls_alice;

DROP POLICY p1 ON document;

DROP POLICY p2 ON document;

DROP POLICY p3 ON document;

CREATE POLICY p3_with_default ON document
    FOR UPDATE
        USING (cid = (
            SELECT
                cid
            FROM
                category
            WHERE
                cname = 'novel'));

SET SESSION AUTHORIZATION regress_rls_bob;

-- Just because WCO-style enforcement of USING quals occurs with
-- existing/target tuple does not mean that the implementation can be allowed
-- to fail to also enforce this qual against the final tuple appended to
-- relation (since in the absence of an explicit WCO, this is also interpreted
-- as an UPDATE/ALL WCO in general).
--
-- UPDATE path is taken here (fails due to existing tuple).  Note that this is
-- not reported as a "USING expression", because it's an RLS UPDATE check that originated as
-- a USING qual for the purposes of RLS in general, as opposed to an explicit
-- USING qual that is ordinarily a security barrier.  We leave it up to the
-- UPDATE to make this fail:
INSERT INTO document
    VALUES (79, (
            SELECT
                cid
            FROM category
            WHERE
                cname = 'technology'), 1, 'regress_rls_bob', 'technology book, can only insert')
ON CONFLICT (did)
    DO UPDATE SET
        dtitle = EXCLUDED.dtitle
    RETURNING
        *;

-- UPDATE path is taken here.  Existing tuple passes, since its cid
-- corresponds to "novel", but default USING qual is enforced against
-- post-UPDATE tuple too (as always when updating with a policy that lacks an
-- explicit WCO), and so this fails:
INSERT INTO document
    VALUES (2, (
            SELECT
                cid
            FROM category
            WHERE
                cname = 'technology'), 1, 'regress_rls_bob', 'my first novel')
ON CONFLICT (did)
    DO UPDATE SET
        cid = EXCLUDED.cid,
        dtitle = EXCLUDED.dtitle
    RETURNING
        *;

SET SESSION AUTHORIZATION regress_rls_alice;

DROP POLICY p3_with_default ON document;

--
-- Test ALL policies with ON CONFLICT DO UPDATE (much the same as existing UPDATE
-- tests)
--
CREATE POLICY p3_with_all ON document
    FOR ALL
        USING (cid = (
            SELECT
                cid
            FROM
                category
            WHERE
                cname = 'novel'))
            WITH CHECK (dauthor = CURRENT_USER);

SET SESSION AUTHORIZATION regress_rls_bob;

-- Fails, since ALL WCO is enforced in insert path:
INSERT INTO document
    VALUES (80, (
            SELECT
                cid
            FROM category
            WHERE
                cname = 'novel'), 1, 'regress_rls_carol', 'my first novel')
ON CONFLICT (did)
    DO UPDATE SET
        dtitle = EXCLUDED.dtitle,
        cid = 33;

-- Fails, since ALL policy USING qual is enforced (existing, target tuple is in
-- violation, since it has the "manga" cid):
INSERT INTO document
    VALUES (4, (
            SELECT
                cid
            FROM category
            WHERE
                cname = 'novel'), 1, 'regress_rls_bob', 'my first novel')
ON CONFLICT (did)
    DO UPDATE SET
        dtitle = EXCLUDED.dtitle;

-- Fails, since ALL WCO are enforced:
INSERT INTO document
    VALUES (1, (
            SELECT
                cid
            FROM category
            WHERE
                cname = 'novel'), 1, 'regress_rls_bob', 'my first novel')
ON CONFLICT (did)
    DO UPDATE SET
        dauthor = 'regress_rls_carol';

--
-- ROLE/GROUP
--
SET SESSION AUTHORIZATION regress_rls_alice;

CREATE TABLE z1 (
    a int,
    b text
);

CREATE TABLE z2 (
    a int,
    b text
);

GRANT SELECT ON z1, z2 TO regress_rls_group1, regress_rls_group2, regress_rls_bob, regress_rls_carol;

INSERT INTO z1
VALUES
    (1, 'aba'),
    (2, 'bbb'),
    (3, 'ccc'),
    (4, 'dad');

CREATE POLICY p1 ON z1 TO regress_rls_group1
    USING (a % 2 = 0);

CREATE POLICY p2 ON z1 TO regress_rls_group2
    USING (a % 2 = 1);

ALTER TABLE z1 ENABLE ROW LEVEL SECURITY;

SET SESSION AUTHORIZATION regress_rls_bob;

SELECT
    *
FROM
    z1
WHERE
    f_leak (b);

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    z1
WHERE
    f_leak (b);

PREPARE plancache_test AS
SELECT
    *
FROM
    z1
WHERE
    f_leak (b);

EXPLAIN (
    COSTS OFF
) EXECUTE plancache_test;

PREPARE plancache_test2 AS
WITH q AS MATERIALIZED (
    SELECT
        *
    FROM
        z1
    WHERE
        f_leak (
            b
))
SELECT
    *
FROM
    q,
    z2;

EXPLAIN (
    COSTS OFF
) EXECUTE plancache_test2;

PREPARE plancache_test3 AS
WITH q AS MATERIALIZED (
    SELECT
        *
    FROM
        z2
)
SELECT
    *
FROM
    q,
    z1
WHERE
    f_leak (z1.b);

EXPLAIN (
    COSTS OFF
) EXECUTE plancache_test3;

SET ROLE regress_rls_group1;

SELECT
    *
FROM
    z1
WHERE
    f_leak (b);

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    z1
WHERE
    f_leak (b);

EXPLAIN (
    COSTS OFF
) EXECUTE plancache_test;

EXPLAIN (
    COSTS OFF
) EXECUTE plancache_test2;

EXPLAIN (
    COSTS OFF
) EXECUTE plancache_test3;

SET SESSION AUTHORIZATION regress_rls_carol;

SELECT
    *
FROM
    z1
WHERE
    f_leak (b);

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    z1
WHERE
    f_leak (b);

EXPLAIN (
    COSTS OFF
) EXECUTE plancache_test;

EXPLAIN (
    COSTS OFF
) EXECUTE plancache_test2;

EXPLAIN (
    COSTS OFF
) EXECUTE plancache_test3;

SET ROLE regress_rls_group2;

SELECT
    *
FROM
    z1
WHERE
    f_leak (b);

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    z1
WHERE
    f_leak (b);

EXPLAIN (
    COSTS OFF
) EXECUTE plancache_test;

EXPLAIN (
    COSTS OFF
) EXECUTE plancache_test2;

EXPLAIN (
    COSTS OFF
) EXECUTE plancache_test3;

--
-- Views should follow policy for view owner.
--
-- View and Table owner are the same.
SET SESSION AUTHORIZATION regress_rls_alice;

CREATE VIEW rls_view AS
SELECT
    *
FROM
    z1
WHERE
    f_leak (b);

GRANT SELECT ON rls_view TO regress_rls_bob;

-- Query as role that is not owner of view or table.  Should return all records.
SET SESSION AUTHORIZATION regress_rls_bob;

SELECT
    *
FROM
    rls_view;

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    rls_view;

-- Query as view/table owner.  Should return all records.
SET SESSION AUTHORIZATION regress_rls_alice;

SELECT
    *
FROM
    rls_view;

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    rls_view;

DROP VIEW rls_view;

-- View and Table owners are different.
SET SESSION AUTHORIZATION regress_rls_bob;

CREATE VIEW rls_view AS
SELECT
    *
FROM
    z1
WHERE
    f_leak (b);

GRANT SELECT ON rls_view TO regress_rls_alice;

-- Query as role that is not owner of view but is owner of table.
-- Should return records based on view owner policies.
SET SESSION AUTHORIZATION regress_rls_alice;

SELECT
    *
FROM
    rls_view;

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    rls_view;

-- Query as role that is not owner of table but is owner of view.
-- Should return records based on view owner policies.
SET SESSION AUTHORIZATION regress_rls_bob;

SELECT
    *
FROM
    rls_view;

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    rls_view;

-- Query as role that is not the owner of the table or view without permissions.
SET SESSION AUTHORIZATION regress_rls_carol;

SELECT
    *
FROM
    rls_view;

--fail - permission denied.
EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    rls_view;

--fail - permission denied.
-- Query as role that is not the owner of the table or view with permissions.
SET SESSION AUTHORIZATION regress_rls_bob;

GRANT SELECT ON rls_view TO regress_rls_carol;

SELECT
    *
FROM
    rls_view;

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    rls_view;

SET SESSION AUTHORIZATION regress_rls_bob;

DROP VIEW rls_view;

--
-- Command specific
--
SET SESSION AUTHORIZATION regress_rls_alice;

CREATE TABLE x1 (
    a int,
    b text,
    c text
);

GRANT ALL ON x1 TO PUBLIC;

INSERT INTO x1
VALUES
    (1, 'abc', 'regress_rls_bob'),
    (2, 'bcd', 'regress_rls_bob'),
    (3, 'cde', 'regress_rls_carol'),
    (4, 'def', 'regress_rls_carol'),
    (5, 'efg', 'regress_rls_bob'),
    (6, 'fgh', 'regress_rls_bob'),
    (7, 'fgh', 'regress_rls_carol'),
    (8, 'fgh', 'regress_rls_carol');

CREATE POLICY p0 ON x1
    FOR ALL
        USING (c = CURRENT_USER);

CREATE POLICY p1 ON x1
    FOR SELECT
        USING (a % 2 = 0);

CREATE POLICY p2 ON x1
    FOR INSERT
        WITH CHECK (a % 2 = 1);

CREATE POLICY p3 ON x1
    FOR UPDATE
        USING (a % 2 = 0);

CREATE POLICY p4 ON x1
    FOR DELETE
        USING (a < 8);

ALTER TABLE x1 ENABLE ROW LEVEL SECURITY;

SET SESSION AUTHORIZATION regress_rls_bob;

SELECT
    *
FROM
    x1
WHERE
    f_leak (b)
ORDER BY
    a ASC;

UPDATE
    x1
SET
    b = b || '_updt'
WHERE
    f_leak (b)
RETURNING
    *;

SET SESSION AUTHORIZATION regress_rls_carol;

SELECT
    *
FROM
    x1
WHERE
    f_leak (b)
ORDER BY
    a ASC;

UPDATE
    x1
SET
    b = b || '_updt'
WHERE
    f_leak (b)
RETURNING
    *;

DELETE FROM x1
WHERE f_leak (b)
RETURNING
    *;

--
-- Duplicate Policy Names
--
SET SESSION AUTHORIZATION regress_rls_alice;

CREATE TABLE y1 (
    a int,
    b text
);

CREATE TABLE y2 (
    a int,
    b text
);

GRANT ALL ON y1, y2 TO regress_rls_bob;

CREATE POLICY p1 ON y1
    FOR ALL
        USING (a % 2 = 0);

CREATE POLICY p2 ON y1
    FOR SELECT
        USING (a > 2);

CREATE POLICY p1 ON y1
    FOR SELECT
        USING (a % 2 = 1);

--fail
CREATE POLICY p1 ON y2
    FOR ALL
        USING (a % 2 = 0);

--OK
ALTER TABLE y1 ENABLE ROW LEVEL SECURITY;

ALTER TABLE y2 ENABLE ROW LEVEL SECURITY;

--
-- Expression structure with SBV
--
-- Create view as table owner.  RLS should NOT be applied.
SET SESSION AUTHORIZATION regress_rls_alice;

CREATE VIEW rls_sbv WITH ( security_barrier
) AS
SELECT
    *
FROM
    y1
WHERE
    f_leak (
        b);

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    rls_sbv
WHERE (a = 1);

DROP VIEW rls_sbv;

-- Create view as role that does not own table.  RLS should be applied.
SET SESSION AUTHORIZATION regress_rls_bob;

CREATE VIEW rls_sbv WITH ( security_barrier
) AS
SELECT
    *
FROM
    y1
WHERE
    f_leak (
        b);

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    rls_sbv
WHERE (a = 1);

DROP VIEW rls_sbv;

--
-- Expression structure
--
SET SESSION AUTHORIZATION regress_rls_alice;

INSERT INTO y2 (
    SELECT
        x,
        md5(x::text)
    FROM
        generate_series(0, 20) x);

CREATE POLICY p2 ON y2
    USING (a % 3 = 0);

CREATE POLICY p3 ON y2
    USING (a % 4 = 0);

SET SESSION AUTHORIZATION regress_rls_bob;

SELECT
    *
FROM
    y2
WHERE
    f_leak (b);

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    y2
WHERE
    f_leak (b);

--
-- Qual push-down of leaky functions, when not referring to table
--
SELECT
    *
FROM
    y2
WHERE
    f_leak ('abc');

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    y2
WHERE
    f_leak ('abc');

CREATE TABLE test_qual_pushdown (
    abc text
);

INSERT INTO test_qual_pushdown
VALUES
    ('abc'),
    ('def');

SELECT
    *
FROM
    y2
    JOIN test_qual_pushdown ON (b = abc)
WHERE
    f_leak (abc);

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    y2
    JOIN test_qual_pushdown ON (b = abc)
WHERE
    f_leak (abc);

SELECT
    *
FROM
    y2
    JOIN test_qual_pushdown ON (b = abc)
WHERE
    f_leak (b);

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    y2
    JOIN test_qual_pushdown ON (b = abc)
WHERE
    f_leak (b);

DROP TABLE test_qual_pushdown;

--
-- Plancache invalidate on user change.
--
RESET SESSION AUTHORIZATION;

DROP TABLE t1 CASCADE;

CREATE TABLE t1 (
    a integer
);

GRANT SELECT ON t1 TO regress_rls_bob, regress_rls_carol;

CREATE POLICY p1 ON t1 TO regress_rls_bob
    USING ((a % 2) = 0);

CREATE POLICY p2 ON t1 TO regress_rls_carol
    USING ((a % 4) = 0);

ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;

-- Prepare as regress_rls_bob
SET ROLE regress_rls_bob;

PREPARE role_inval AS
SELECT
    *
FROM
    t1;

-- Check plan
EXPLAIN (
    COSTS OFF
) EXECUTE role_inval;

-- Change to regress_rls_carol
SET ROLE regress_rls_carol;

-- Check plan- should be different
EXPLAIN (
    COSTS OFF
) EXECUTE role_inval;

-- Change back to regress_rls_bob
SET ROLE regress_rls_bob;

-- Check plan- should be back to original
EXPLAIN (
    COSTS OFF
) EXECUTE role_inval;

--
-- CTE and RLS
--
RESET SESSION AUTHORIZATION;

DROP TABLE t1 CASCADE;

CREATE TABLE t1 (
    a integer,
    b text
);

CREATE POLICY p1 ON t1
    USING (a % 2 = 0);

ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;

GRANT ALL ON t1 TO regress_rls_bob;

INSERT INTO t1 (
    SELECT
        x,
        md5(x::text)
    FROM
        generate_series(0, 20) x);

SET SESSION AUTHORIZATION regress_rls_bob;

WITH cte1 AS MATERIALIZED (
    SELECT
        *
    FROM
        t1
    WHERE
        f_leak (
            b
))
SELECT
    *
FROM
    cte1;

EXPLAIN (
    COSTS OFF
) WITH cte1 AS MATERIALIZED (
    SELECT
        *
    FROM
        t1
    WHERE
        f_leak (b))
SELECT
    *
FROM
    cte1;

WITH cte1 AS (
    UPDATE
        t1
    SET
        a = a + 1
    RETURNING
        *
)
SELECT
    *
FROM
    cte1;

--fail
WITH cte1 AS (
    UPDATE
        t1
    SET
        a = a
    RETURNING
        *
)
SELECT
    *
FROM
    cte1;

--ok
WITH cte1 AS (
INSERT INTO t1
        VALUES (21, 'Fail')
    RETURNING
        *)
    SELECT
        *
    FROM
        cte1;

--fail
WITH cte1 AS (
INSERT INTO t1
        VALUES (20, 'Success')
    RETURNING
        *)
    SELECT
        *
    FROM
        cte1;

--ok
--
-- Rename Policy
--
RESET SESSION AUTHORIZATION;

ALTER POLICY p1 ON t1 RENAME TO p1;

--fail
SELECT
    polname,
    relname
FROM
    pg_policy pol
    JOIN pg_class pc ON (pc.oid = pol.polrelid)
WHERE
    relname = 't1';

ALTER POLICY p1 ON t1 RENAME TO p2;

--ok
SELECT
    polname,
    relname
FROM
    pg_policy pol
    JOIN pg_class pc ON (pc.oid = pol.polrelid)
WHERE
    relname = 't1';

--
-- Check INSERT SELECT
--
SET SESSION AUTHORIZATION regress_rls_bob;

CREATE TABLE t2 (
    a integer,
    b text
);

INSERT INTO t2 (
    SELECT
        *
    FROM
        t1);

EXPLAIN (
    COSTS OFF
) INSERT INTO t2 (
    SELECT
        *
    FROM
        t1);

SELECT
    *
FROM
    t2;

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    t2;

CREATE TABLE t3 AS
SELECT
    *
FROM
    t1;

SELECT
    *
FROM
    t3;

SELECT
    * INTO t4
FROM
    t1;

SELECT
    *
FROM
    t4;

--
-- RLS with JOIN
--
SET SESSION AUTHORIZATION regress_rls_alice;

CREATE TABLE blog (
    id integer,
    author text,
    post text
);

CREATE TABLE comment (
    blog_id integer,
    message text
);

GRANT ALL ON blog, comment TO regress_rls_bob;

CREATE POLICY blog_1 ON blog
    USING (id % 2 = 0);

ALTER TABLE blog ENABLE ROW LEVEL SECURITY;

INSERT INTO blog
VALUES
    (1, 'alice', 'blog #1'),
    (2, 'bob', 'blog #1'),
    (3, 'alice', 'blog #2'),
    (4, 'alice', 'blog #3'),
    (5, 'john', 'blog #1');

INSERT INTO comment
VALUES
    (1, 'cool blog'),
    (1, 'fun blog'),
    (3, 'crazy blog'),
    (5, 'what?'),
    (4, 'insane!'),
    (2, 'who did it?');

SET SESSION AUTHORIZATION regress_rls_bob;

-- Check RLS JOIN with Non-RLS.
SELECT
    id,
    author,
    message
FROM
    blog
    JOIN COMMENT ON id = blog_id;

-- Check Non-RLS JOIN with RLS.
SELECT
    id,
    author,
    message
FROM
    comment
    JOIN blog ON id = blog_id;

SET SESSION AUTHORIZATION regress_rls_alice;

CREATE POLICY comment_1 ON comment
    USING (blog_id < 4);

ALTER TABLE comment ENABLE ROW LEVEL SECURITY;

SET SESSION AUTHORIZATION regress_rls_bob;

-- Check RLS JOIN RLS
SELECT
    id,
    author,
    message
FROM
    blog
    JOIN COMMENT ON id = blog_id;

SELECT
    id,
    author,
    message
FROM
    comment
    JOIN blog ON id = blog_id;

SET SESSION AUTHORIZATION regress_rls_alice;

DROP TABLE blog, comment;

--
-- Default Deny Policy
--
RESET SESSION AUTHORIZATION;

DROP POLICY p2 ON t1;

ALTER TABLE t1 OWNER TO regress_rls_alice;

-- Check that default deny does not apply to superuser.
RESET SESSION AUTHORIZATION;

SELECT
    *
FROM
    t1;

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    t1;

-- Check that default deny does not apply to table owner.
SET SESSION AUTHORIZATION regress_rls_alice;

SELECT
    *
FROM
    t1;

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    t1;

-- Check that default deny applies to non-owner/non-superuser when RLS on.
SET SESSION AUTHORIZATION regress_rls_bob;

SET row_security TO ON;

SELECT
    *
FROM
    t1;

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    t1;

SET SESSION AUTHORIZATION regress_rls_bob;

SELECT
    *
FROM
    t1;

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    t1;

--
-- COPY TO/FROM
--
RESET SESSION AUTHORIZATION;

DROP TABLE copy_t CASCADE;

CREATE TABLE copy_t (
    a integer,
    b text
);

CREATE POLICY p1 ON copy_t
    USING (a % 2 = 0);

ALTER TABLE copy_t ENABLE ROW LEVEL SECURITY;

GRANT ALL ON copy_t TO regress_rls_bob, regress_rls_exempt_user;

INSERT INTO copy_t (
    SELECT
        x,
        md5(x::text)
    FROM
        generate_series(0, 10) x);

-- Check COPY TO as Superuser/owner.
RESET SESSION AUTHORIZATION;

SET row_security TO OFF;

COPY (
    SELECT
        *
    FROM
        copy_t
    ORDER BY
        a ASC)
    TO STDOUT WITH DELIMITER ',';

SET row_security TO ON;

COPY (
    SELECT
        *
    FROM
        copy_t
    ORDER BY
        a ASC)
    TO STDOUT WITH DELIMITER ',';

-- Check COPY TO as user with permissions.
SET SESSION AUTHORIZATION regress_rls_bob;

SET row_security TO OFF;

COPY (
    SELECT
        *
    FROM
        copy_t
    ORDER BY
        a ASC)
    TO STDOUT WITH DELIMITER ',';

--fail - would be affected by RLS
SET row_security TO ON;

COPY (
    SELECT
        *
    FROM
        copy_t
    ORDER BY
        a ASC)
    TO STDOUT WITH DELIMITER ',';

--ok
-- Check COPY TO as user with permissions and BYPASSRLS
SET SESSION AUTHORIZATION regress_rls_exempt_user;

SET row_security TO OFF;

COPY (
    SELECT
        *
    FROM
        copy_t
    ORDER BY
        a ASC)
    TO STDOUT WITH DELIMITER ',';

--ok
SET row_security TO ON;

COPY (
    SELECT
        *
    FROM
        copy_t
    ORDER BY
        a ASC)
    TO STDOUT WITH DELIMITER ',';

--ok
-- Check COPY TO as user without permissions. SET row_security TO OFF;
SET SESSION AUTHORIZATION regress_rls_carol;

SET row_security TO OFF;

COPY (
    SELECT
        *
    FROM
        copy_t
    ORDER BY
        a ASC)
    TO STDOUT WITH DELIMITER ',';

--fail - would be affected by RLS
SET row_security TO ON;

COPY (
    SELECT
        *
    FROM
        copy_t
    ORDER BY
        a ASC)
    TO STDOUT WITH DELIMITER ',';

--fail - permission denied
-- Check COPY relation TO; keep it just one row to avoid reordering issues
RESET SESSION AUTHORIZATION;

SET row_security TO ON;

CREATE TABLE copy_rel_to (
    a integer,
    b text
);

CREATE POLICY p1 ON copy_rel_to
    USING (a % 2 = 0);

ALTER TABLE copy_rel_to ENABLE ROW LEVEL SECURITY;

GRANT ALL ON copy_rel_to TO regress_rls_bob, regress_rls_exempt_user;

INSERT INTO copy_rel_to
    VALUES (1, md5('1'));

-- Check COPY TO as Superuser/owner.
RESET SESSION AUTHORIZATION;

SET row_security TO OFF;

COPY copy_rel_to TO STDOUT WITH DELIMITER ',';

SET row_security TO ON;

COPY copy_rel_to TO STDOUT WITH DELIMITER ',';

-- Check COPY TO as user with permissions.
SET SESSION AUTHORIZATION regress_rls_bob;

SET row_security TO OFF;

COPY copy_rel_to TO STDOUT WITH DELIMITER ',';

--fail - would be affected by RLS
SET row_security TO ON;

COPY copy_rel_to TO STDOUT WITH DELIMITER ',';

--ok
-- Check COPY TO as user with permissions and BYPASSRLS
SET SESSION AUTHORIZATION regress_rls_exempt_user;

SET row_security TO OFF;

COPY copy_rel_to TO STDOUT WITH DELIMITER ',';

--ok
SET row_security TO ON;

COPY copy_rel_to TO STDOUT WITH DELIMITER ',';

--ok
-- Check COPY TO as user without permissions. SET row_security TO OFF;
SET SESSION AUTHORIZATION regress_rls_carol;

SET row_security TO OFF;

COPY copy_rel_to TO STDOUT WITH DELIMITER ',';

--fail - permission denied
SET row_security TO ON;

COPY copy_rel_to TO STDOUT WITH DELIMITER ',';

--fail - permission denied
-- Check COPY FROM as Superuser/owner.
RESET SESSION AUTHORIZATION;

SET row_security TO OFF;

SET row_security TO ON;

-- Check COPY FROM as user with permissions.
SET SESSION AUTHORIZATION regress_rls_bob;

SET row_security TO OFF;

COPY copy_t
FROM
    STDIN;

--fail - would be affected by RLS.
SET row_security TO ON;

COPY copy_t
FROM
    STDIN;

--fail - COPY FROM not supported by RLS.
-- Check COPY FROM as user with permissions and BYPASSRLS
SET SESSION AUTHORIZATION regress_rls_exempt_user;

SET row_security TO ON;

-- Check COPY FROM as user without permissions.
SET SESSION AUTHORIZATION regress_rls_carol;

SET row_security TO OFF;

COPY copy_t
FROM
    STDIN;

--fail - permission denied.
SET row_security TO ON;

COPY copy_t
FROM
    STDIN;

--fail - permission denied.
RESET SESSION AUTHORIZATION;

DROP TABLE copy_t;

DROP TABLE copy_rel_to CASCADE;

-- Check WHERE CURRENT OF
SET SESSION AUTHORIZATION regress_rls_alice;

CREATE TABLE current_check (
    currentid int,
    payload text,
    rlsuser text
);

GRANT ALL ON current_check TO PUBLIC;

INSERT INTO current_check
VALUES
    (1, 'abc', 'regress_rls_bob'),
    (2, 'bcd', 'regress_rls_bob'),
    (3, 'cde', 'regress_rls_bob'),
    (4, 'def', 'regress_rls_bob');

CREATE POLICY p1 ON current_check
    FOR SELECT
        USING (currentid % 2 = 0);

CREATE POLICY p2 ON current_check
    FOR DELETE
        USING (currentid = 4
            AND rlsuser = CURRENT_USER);

CREATE POLICY p3 ON current_check
    FOR UPDATE
        USING (currentid = 4)
        WITH CHECK (rlsuser = CURRENT_USER);

ALTER TABLE current_check ENABLE ROW LEVEL SECURITY;

SET SESSION AUTHORIZATION regress_rls_bob;

-- Can SELECT even rows
SELECT
    *
FROM
    current_check;

-- Cannot UPDATE row 2
UPDATE
    current_check
SET
    payload = payload || '_new'
WHERE
    currentid = 2
RETURNING
    *;

BEGIN;
DECLARE current_check_cursor SCROLL CURSOR FOR
    SELECT
        *
    FROM
        current_check;
-- Returns rows that can be seen according to SELECT policy, like plain SELECT
-- above (even rows)
FETCH ABSOLUTE 1
FROM
    current_check_cursor;
-- Still cannot UPDATE row 2 through cursor
UPDATE
    current_check
SET
    payload = payload || '_new'
WHERE
    CURRENT OF current_check_cursor
RETURNING
    *;
-- Can update row 4 through cursor, which is the next visible row
FETCH RELATIVE 1
FROM
    current_check_cursor;
UPDATE
    current_check
SET
    payload = payload || '_new'
WHERE
    CURRENT OF current_check_cursor
RETURNING
    *;
SELECT
    *
FROM
    current_check;
-- Plan should be a subquery TID scan
EXPLAIN (
    COSTS OFF
) UPDATE
    current_check
SET
    payload = payload
WHERE
    CURRENT OF current_check_cursor;
-- Similarly can only delete row 4
FETCH ABSOLUTE 1
FROM
    current_check_cursor;
DELETE FROM current_check
WHERE CURRENT OF current_check_cursor
RETURNING
    *;
FETCH RELATIVE 1 FROM current_check_cursor;
DELETE FROM current_check
WHERE CURRENT OF current_check_cursor
RETURNING
    *;
SELECT
    *
FROM
    current_check;
COMMIT;

--
-- check pg_stats view filtering
--
SET row_security TO ON;

SET SESSION AUTHORIZATION regress_rls_alice;

ANALYZE current_check;

-- Stats visible
SELECT
    row_security_active ('current_check');

SELECT
    attname,
    most_common_vals
FROM
    pg_stats
WHERE
    tablename = 'current_check'
ORDER BY
    1;

SET SESSION AUTHORIZATION regress_rls_bob;

-- Stats not visible
SELECT
    row_security_active ('current_check');

SELECT
    attname,
    most_common_vals
FROM
    pg_stats
WHERE
    tablename = 'current_check'
ORDER BY
    1;

--
-- Collation support
--
BEGIN;
CREATE TABLE coll_t (
    c
) AS
VALUES (
    'bar'::text
);
CREATE POLICY coll_p ON coll_t
    USING (c < ('foo'::text COLLATE "C"));
ALTER TABLE coll_t ENABLE ROW LEVEL SECURITY;
GRANT SELECT ON coll_t TO regress_rls_alice;
SELECT
    (string_to_array(polqual, ':'))[7] AS inputcollid
FROM
    pg_policy
WHERE
    polrelid = 'coll_t'::regclass;
SET SESSION AUTHORIZATION regress_rls_alice;
SELECT
    *
FROM
    coll_t;
ROLLBACK;

--
-- Shared Object Dependencies
--
RESET SESSION AUTHORIZATION;

BEGIN;
CREATE ROLE regress_rls_eve;
CREATE ROLE regress_rls_frank;
CREATE TABLE tbl1 (
    c
) AS
VALUES (
    'bar'::text
);
GRANT SELECT ON TABLE tbl1 TO regress_rls_eve;
CREATE POLICY P ON tbl1 TO regress_rls_eve, regress_rls_frank
    USING (TRUE);
SELECT
    refclassid::regclass,
    deptype
FROM
    pg_depend
WHERE
    classid = 'pg_policy'::regclass
    AND refobjid = 'tbl1'::regclass;
SELECT
    refclassid::regclass,
    deptype
FROM
    pg_shdepend
WHERE
    classid = 'pg_policy'::regclass
    AND refobjid IN ('regress_rls_eve'::regrole, 'regress_rls_frank'::regrole);
SAVEPOINT q;
DROP ROLE regress_rls_eve;
--fails due to dependency on POLICY p
ROLLBACK TO q;

ALTER POLICY p ON tbl1 TO regress_rls_frank
    USING (TRUE);

SAVEPOINT q;

DROP ROLE regress_rls_eve;

--fails due to dependency on GRANT SELECT
ROLLBACK TO q;

REVOKE ALL ON TABLE tbl1 FROM regress_rls_eve;

SAVEPOINT q;

DROP ROLE regress_rls_eve;

--succeeds
ROLLBACK TO q;

SAVEPOINT q;

DROP ROLE regress_rls_frank;

--fails due to dependency on POLICY p
ROLLBACK TO q;

DROP POLICY p ON tbl1;

SAVEPOINT q;

DROP ROLE regress_rls_frank;

-- succeeds
ROLLBACK TO q;

ROLLBACK;

-- cleanup
--
-- Converting table to view
--
BEGIN;
CREATE TABLE t (
    c int
);
CREATE POLICY p ON t
    USING (c % 2 = 1);
ALTER TABLE t ENABLE ROW LEVEL SECURITY;
SAVEPOINT q;
CREATE RULE "_RETURN" AS ON
SELECT
    TO t
        DO INSTEAD
        SELECT
            * FROM
            generate_series(1, 5) t0 (c);
-- fails due to row level security enabled
ROLLBACK TO q;

ALTER TABLE t DISABLE ROW LEVEL SECURITY;

SAVEPOINT q;

CREATE RULE "_RETURN" AS ON
SELECT
    TO t
        DO INSTEAD
        SELECT
            * FROM
            generate_series(1, 5) t0 (c);

-- fails due to policy p on t
ROLLBACK TO q;

DROP POLICY p ON t;

CREATE RULE "_RETURN" AS ON
SELECT
    TO t
        DO INSTEAD
        SELECT
            * FROM
            generate_series(1, 5) t0 (c);

-- succeeds
ROLLBACK;

--
-- Policy expression handling
--
BEGIN;
CREATE TABLE t (
    c
) AS
VALUES (
    'bar'::text
);
CREATE POLICY p ON t
    USING (max(c));
-- fails: aggregate functions are not allowed in policy expressions
ROLLBACK;

--
-- Non-target relations are only subject to SELECT policies
--
SET SESSION AUTHORIZATION regress_rls_alice;

CREATE TABLE r1 (
    a int
);

CREATE TABLE r2 (
    a int
);

INSERT INTO r1
VALUES
    (10),
    (20);

INSERT INTO r2
VALUES
    (10),
    (20);

GRANT ALL ON r1, r2 TO regress_rls_bob;

CREATE POLICY p1 ON r1
    USING (TRUE);

ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;

CREATE POLICY p1 ON r2
    FOR SELECT
        USING (TRUE);

CREATE POLICY p2 ON r2
    FOR INSERT
        WITH CHECK (FALSE);

CREATE POLICY p3 ON r2
    FOR UPDATE
        USING (FALSE);

CREATE POLICY p4 ON r2
    FOR DELETE
        USING (FALSE);

ALTER TABLE r2 ENABLE ROW LEVEL SECURITY;

SET SESSION AUTHORIZATION regress_rls_bob;

SELECT
    *
FROM
    r1;

SELECT
    *
FROM
    r2;

-- r2 is read-only
INSERT INTO r2
    VALUES (2);

-- Not allowed
UPDATE
    r2
SET
    a = 2
RETURNING
    *;

-- Updates nothing
DELETE FROM r2
RETURNING
    *;

-- Deletes nothing
-- r2 can be used as a non-target relation in DML
INSERT INTO r1
SELECT
    a + 1
FROM
    r2
RETURNING
    *;

-- OK
UPDATE
    r1
SET
    a = r2.a + 2
FROM
    r2
WHERE
    r1.a = r2.a
RETURNING
    *;

-- OK
DELETE FROM r1 USING r2
WHERE r1.a = r2.a + 2
RETURNING
    *;

-- OK
SELECT
    *
FROM
    r1;

SELECT
    *
FROM
    r2;

SET SESSION AUTHORIZATION regress_rls_alice;

DROP TABLE r1;

DROP TABLE r2;

--
-- FORCE ROW LEVEL SECURITY applies RLS to owners too
--
SET SESSION AUTHORIZATION regress_rls_alice;

SET row_security = ON;

CREATE TABLE r1 (
    a int
);

INSERT INTO r1
VALUES
    (10),
    (20);

CREATE POLICY p1 ON r1
    USING (FALSE);

ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;

ALTER TABLE r1 FORCE ROW LEVEL SECURITY;

-- No error, but no rows
TABLE r1;

-- RLS error
INSERT INTO r1
    VALUES (1);

-- No error (unable to see any rows to update)
UPDATE
    r1
SET
    a = 1;

TABLE r1;

-- No error (unable to see any rows to delete)
DELETE FROM r1;

TABLE r1;

SET row_security = OFF;

-- these all fail, would be affected by RLS
TABLE r1;

UPDATE
    r1
SET
    a = 1;

DELETE FROM r1;

DROP TABLE r1;

--
-- FORCE ROW LEVEL SECURITY does not break RI
--
SET SESSION AUTHORIZATION regress_rls_alice;

SET row_security = ON;

CREATE TABLE r1 (
    a int PRIMARY KEY
);

CREATE TABLE r2 (
    a int REFERENCES r1
);

INSERT INTO r1
VALUES
    (10),
    (20);

INSERT INTO r2
VALUES
    (10),
    (20);

-- Create policies on r2 which prevent the
-- owner from seeing any rows, but RI should
-- still see them.
CREATE POLICY p1 ON r2
    USING (FALSE);

ALTER TABLE r2 ENABLE ROW LEVEL SECURITY;

ALTER TABLE r2 FORCE ROW LEVEL SECURITY;

-- Errors due to rows in r2
DELETE FROM r1;

-- Reset r2 to no-RLS
DROP POLICY p1 ON r2;

ALTER TABLE r2 NO FORCE ROW LEVEL SECURITY;

ALTER TABLE r2 DISABLE ROW LEVEL SECURITY;

-- clean out r2 for INSERT test below
DELETE FROM r2;

-- Change r1 to not allow rows to be seen
CREATE POLICY p1 ON r1
    USING (FALSE);

ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;

ALTER TABLE r1 FORCE ROW LEVEL SECURITY;

-- No rows seen
TABLE r1;

-- No error, RI still sees that row exists in r1
INSERT INTO r2
    VALUES (10);

DROP TABLE r2;

DROP TABLE r1;

-- Ensure cascaded DELETE works
CREATE TABLE r1 (
    a int PRIMARY KEY
);

CREATE TABLE r2 (
    a int REFERENCES r1 ON DELETE CASCADE
);

INSERT INTO r1
VALUES
    (10),
    (20);

INSERT INTO r2
VALUES
    (10),
    (20);

-- Create policies on r2 which prevent the
-- owner from seeing any rows, but RI should
-- still see them.
CREATE POLICY p1 ON r2
    USING (FALSE);

ALTER TABLE r2 ENABLE ROW LEVEL SECURITY;

ALTER TABLE r2 FORCE ROW LEVEL SECURITY;

-- Deletes all records from both
DELETE FROM r1;

-- Remove FORCE from r2
ALTER TABLE r2 NO FORCE ROW LEVEL SECURITY;

-- As owner, we now bypass RLS
-- verify no rows in r2 now
TABLE r2;

DROP TABLE r2;

DROP TABLE r1;

-- Ensure cascaded UPDATE works
CREATE TABLE r1 (
    a int PRIMARY KEY
);

CREATE TABLE r2 (
    a int REFERENCES r1 ON UPDATE CASCADE
);

INSERT INTO r1
VALUES
    (10),
    (20);

INSERT INTO r2
VALUES
    (10),
    (20);

-- Create policies on r2 which prevent the
-- owner from seeing any rows, but RI should
-- still see them.
CREATE POLICY p1 ON r2
    USING (FALSE);

ALTER TABLE r2 ENABLE ROW LEVEL SECURITY;

ALTER TABLE r2 FORCE ROW LEVEL SECURITY;

-- Updates records in both
UPDATE
    r1
SET
    a = a + 5;

-- Remove FORCE from r2
ALTER TABLE r2 NO FORCE ROW LEVEL SECURITY;

-- As owner, we now bypass RLS
-- verify records in r2 updated
TABLE r2;

DROP TABLE r2;

DROP TABLE r1;

--
-- Test INSERT+RETURNING applies SELECT policies as
-- WithCheckOptions (meaning an error is thrown)
--
SET SESSION AUTHORIZATION regress_rls_alice;

SET row_security = ON;

CREATE TABLE r1 (
    a int
);

CREATE POLICY p1 ON r1
    FOR SELECT
        USING (FALSE);

CREATE POLICY p2 ON r1
    FOR INSERT
        WITH CHECK (TRUE);

ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;

ALTER TABLE r1 FORCE ROW LEVEL SECURITY;

-- Works fine
INSERT INTO r1
VALUES
    (10),
    (20);

-- No error, but no rows
TABLE r1;

SET row_security = OFF;

-- fail, would be affected by RLS
TABLE r1;

SET row_security = ON;

-- Error
INSERT INTO r1
VALUES
    (10),
    (20)
RETURNING
    *;

DROP TABLE r1;

--
-- Test UPDATE+RETURNING applies SELECT policies as
-- WithCheckOptions (meaning an error is thrown)
--
SET SESSION AUTHORIZATION regress_rls_alice;

SET row_security = ON;

CREATE TABLE r1 (
    a int PRIMARY KEY
);

CREATE POLICY p1 ON r1
    FOR SELECT
        USING (a < 20);

CREATE POLICY p2 ON r1
    FOR UPDATE
        USING (a < 20)
        WITH CHECK (TRUE);

CREATE POLICY p3 ON r1
    FOR INSERT
        WITH CHECK (TRUE);

INSERT INTO r1
    VALUES (10);

ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;

ALTER TABLE r1 FORCE ROW LEVEL SECURITY;

-- Works fine
UPDATE
    r1
SET
    a = 30;

-- Show updated rows
ALTER TABLE r1 NO FORCE ROW LEVEL SECURITY;

TABLE r1;

-- reset value in r1 for test with RETURNING
UPDATE
    r1
SET
    a = 10;

-- Verify row reset
TABLE r1;

ALTER TABLE r1 FORCE ROW LEVEL SECURITY;

-- Error
UPDATE
    r1
SET
    a = 30
RETURNING
    *;

-- UPDATE path of INSERT ... ON CONFLICT DO UPDATE should also error out
INSERT INTO r1
    VALUES (10)
ON CONFLICT (a)
    DO UPDATE SET
        a = 30
    RETURNING
        *;

-- Should still error out without RETURNING (use of arbiter always requires
-- SELECT permissions)
INSERT INTO r1
    VALUES (10)
ON CONFLICT (a)
    DO UPDATE SET
        a = 30;

INSERT INTO r1
    VALUES (10)
ON CONFLICT ON CONSTRAINT r1_pkey
    DO UPDATE SET
        a = 30;

DROP TABLE r1;

-- Check dependency handling
RESET SESSION AUTHORIZATION;

CREATE TABLE dep1 (
    c1 int
);

CREATE TABLE dep2 (
    c1 int
);

CREATE POLICY dep_p1 ON dep1 TO regress_rls_bob
    USING (c1 > (
        SELECT
            max(dep2.c1)
        FROM
            dep2));

ALTER POLICY dep_p1 ON dep1 TO regress_rls_bob, regress_rls_carol;

-- Should return one
SELECT
    count(*) = 1
FROM
    pg_depend
WHERE
    objid = (
        SELECT
            oid
        FROM
            pg_policy
        WHERE
            polname = 'dep_p1')
    AND refobjid = (
        SELECT
            oid
        FROM
            pg_class
        WHERE
            relname = 'dep2');

ALTER POLICY dep_p1 ON dep1
    USING (TRUE);

-- Should return one
SELECT
    count(*) = 1
FROM
    pg_shdepend
WHERE
    objid = (
        SELECT
            oid
        FROM
            pg_policy
        WHERE
            polname = 'dep_p1')
    AND refobjid = (
        SELECT
            oid
        FROM
            pg_authid
        WHERE
            rolname = 'regress_rls_bob');

-- Should return one
SELECT
    count(*) = 1
FROM
    pg_shdepend
WHERE
    objid = (
        SELECT
            oid
        FROM
            pg_policy
        WHERE
            polname = 'dep_p1')
    AND refobjid = (
        SELECT
            oid
        FROM
            pg_authid
        WHERE
            rolname = 'regress_rls_carol');

-- Should return zero
SELECT
    count(*) = 0
FROM
    pg_depend
WHERE
    objid = (
        SELECT
            oid
        FROM
            pg_policy
        WHERE
            polname = 'dep_p1')
    AND refobjid = (
        SELECT
            oid
        FROM
            pg_class
        WHERE
            relname = 'dep2');

-- DROP OWNED BY testing
RESET SESSION AUTHORIZATION;

CREATE ROLE regress_rls_dob_role1;

CREATE ROLE regress_rls_dob_role2;

CREATE TABLE dob_t1 (
    c1 int
);

CREATE TABLE dob_t2 (
    c1 int
)
PARTITION BY RANGE (c1);

CREATE POLICY p1 ON dob_t1 TO regress_rls_dob_role1
    USING (TRUE);

DROP OWNED BY regress_rls_dob_role1;

DROP POLICY p1 ON dob_t1;

-- should fail, already gone
CREATE POLICY p1 ON dob_t1 TO regress_rls_dob_role1, regress_rls_dob_role2
    USING (TRUE);

DROP OWNED BY regress_rls_dob_role1;

DROP POLICY p1 ON dob_t1;

-- should succeed
CREATE POLICY p1 ON dob_t2 TO regress_rls_dob_role1, regress_rls_dob_role2
    USING (TRUE);

DROP OWNED BY regress_rls_dob_role1;

DROP POLICY p1 ON dob_t2;

-- should succeed
DROP USER regress_rls_dob_role1;

DROP USER regress_rls_dob_role2;

-- Bug #15708: view + table with RLS should check policies as view owner
CREATE TABLE ref_tbl (
    a int
);

INSERT INTO ref_tbl
    VALUES (1);

CREATE TABLE rls_tbl (
    a int
);

INSERT INTO rls_tbl
    VALUES (10);

ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY;

CREATE POLICY p1 ON rls_tbl
    USING (EXISTS (
        SELECT
            1
        FROM
            ref_tbl));

GRANT SELECT ON ref_tbl TO regress_rls_bob;

GRANT SELECT ON rls_tbl TO regress_rls_bob;

CREATE VIEW rls_view AS
SELECT
    *
FROM
    rls_tbl;

ALTER VIEW rls_view OWNER TO regress_rls_bob;

GRANT SELECT ON rls_view TO regress_rls_alice;

SET SESSION AUTHORIZATION regress_rls_alice;

SELECT
    *
FROM
    ref_tbl;

-- Permission denied
SELECT
    *
FROM
    rls_tbl;

-- Permission denied
SELECT
    *
FROM
    rls_view;

-- OK
RESET SESSION AUTHORIZATION;

DROP VIEW rls_view;

DROP TABLE rls_tbl;

DROP TABLE ref_tbl;

--
-- Clean up objects
--
RESET SESSION AUTHORIZATION;

DROP SCHEMA regress_rls_schema CASCADE;

DROP USER regress_rls_alice;

DROP USER regress_rls_bob;

DROP USER regress_rls_carol;

DROP USER regress_rls_dave;

DROP USER regress_rls_exempt_user;

DROP ROLE regress_rls_group1;

DROP ROLE regress_rls_group2;

-- Arrange to have a few policies left over, for testing
-- pg_dump/pg_restore
CREATE SCHEMA regress_rls_schema;

CREATE TABLE rls_tbl (
    c1 int
);

ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY;

CREATE POLICY p1 ON rls_tbl
    USING (c1 > 5);

CREATE POLICY p2 ON rls_tbl
    FOR SELECT
        USING (c1 <= 3);

CREATE POLICY p3 ON rls_tbl
    FOR UPDATE
        USING (c1 <= 3)
        WITH CHECK (c1 > 5);

CREATE POLICY p4 ON rls_tbl
    FOR DELETE
        USING (c1 <= 3);

CREATE TABLE rls_tbl_force (
    c1 int
);

ALTER TABLE rls_tbl_force ENABLE ROW LEVEL SECURITY;

ALTER TABLE rls_tbl_force FORCE ROW LEVEL SECURITY;

CREATE POLICY p1 ON rls_tbl_force
    USING (c1 = 5)
    WITH CHECK (c1 < 5);

CREATE POLICY p2 ON rls_tbl_force
    FOR SELECT
        USING (c1 = 8);

CREATE POLICY p3 ON rls_tbl_force
    FOR UPDATE
        USING (c1 = 8)
        WITH CHECK (c1 >= 5);

CREATE POLICY p4 ON rls_tbl_force
    FOR DELETE
        USING (c1 = 8);

